# -*- coding: utf-8 -*-
# =============================================================================
#     FileName:
#         Desc:
#       Author: GGA
#        Email:
#     HomePage:
#      Version: 1.0.1
#   LastChange: 2020-12-20
#      History:
# =============================================================================
import logging

import fire

from utils.mysql_helper import MySQLServer
from utils.logger_helper import LoggerHelper

logger = LoggerHelper.get_logger()


class MySQLTrigger(object):
    def __init__(self, database_name: str, table_name: str,
                 mysql_host: str = "127.0.0.1", mysql_port: int = 3306,
                 mysql_user: str = "root", mysql_pass: str = ""):
        self.mysql_host = mysql_host
        self.mysql_port = mysql_port
        self.mysql_user = mysql_user
        self.mysql_pass = mysql_pass
        self.database_name = database_name
        self.table_name = table_name
        self.new_table_name = "_" + self.table_name + "_new"
        self.full_columns = self._get_full_columns()
        self.pk_columns = self._get_pk_columns()

    def _get_mysql_client(self):
        return MySQLServer(
            mysql_host=self.mysql_host, mysql_port=self.mysql_port,
            mysql_user=self.mysql_user, mysql_password=self.mysql_pass,
            database_name="mysql"
        )

    def _get_full_columns(self):
        full_columns = list()
        mysql_client = self._get_mysql_client()
        sql_script = "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=%s AND TABLE_NAME=%s;"
        sql_paras = [self.database_name, self.table_name]
        query_rows = mysql_client.mysql_query(sql_script=sql_script, sql_paras=sql_paras, return_dict=True)
        return [item["COLUMN_NAME"] for item in query_rows]

    def _get_pk_columns(self):
        mysql_client = self._get_mysql_client()
        sql_script = """
        SELECT COLUMN_NAME 
        FROM information_schema.COLUMNS 
        WHERE TABLE_SCHEMA=%s 
        AND TABLE_NAME=%s
        AND COLUMN_KEY='PRI'
        """
        sql_paras = [self.database_name, self.table_name]
        query_rows = mysql_client.mysql_query(sql_script=sql_script, sql_paras=sql_paras, return_dict=True)
        return [item["COLUMN_NAME"] for item in query_rows]

    def _get_full_column_union_string(self, table_alias: str = ""):
        new_columns = list()
        table_alias = table_alias.strip()
        for column_name in self.full_columns:
            if table_alias == "":
                new_columns.append("`{}`".format(column_name))
            else:
                new_columns.append("{}.`{}`".format(table_alias, column_name))
        return ",".join(new_columns)

    def _get_pk_column_equal_string(self, left_table_alias: str = "", right_table_alias: str = ""):
        equal_columns = list()
        left_table_alias = left_table_alias.strip()
        right_table_alias = right_table_alias.strip()
        for column_name in self.pk_columns:
            if left_table_alias == "":
                left_column_name = "`{}`".format(column_name)
            else:
                left_column_name = "{}.`{}`".format(left_table_alias, column_name)
            if right_table_alias == "":
                right_column_name = "`{}`".format(column_name)
            else:
                right_column_name = "{}.`{}`".format(right_table_alias, column_name)
            equal_columns.append("{} <=> {} ".format(left_column_name, right_column_name))

        return "AND ".join(equal_columns)

    def _get_insert_trigger(self):
        demo_sql = """
CREATE TRIGGER `{database_name}`.`pt_osc_{database_name}_{table_name}_ins` 
AFTER INSERT ON `{database_name}`.`{table_name}` 
FOR EACH ROW 
REPLACE INTO `{database_name}`.`_{table_name}_new` ({column_items}) 
VALUES ({new_column_items})
"""
        return demo_sql.format(
            database_name=self.database_name,
            table_name=self.table_name,
            column_items=self._get_full_column_union_string(table_alias=""),
            new_column_items=self._get_full_column_union_string("NEW")
        )

    def _get_update_trigger(self):
        demo_sql = """
CREATE TRIGGER `{database_name}`.`pt_osc_{database_name}_{table_name}_upd` 
AFTER UPDATE ON `{database_name}`.`{table_name}` 
FOR EACH ROW 
BEGIN 
    DELETE IGNORE FROM `{database_name}`.`_{table_name}_new` 
    WHERE !({pk_equal_items1}) 
    AND {pk_equal_items2};
    REPLACE INTO `{database_name}`.`_{table_name}_new` ({column_items}) 
    VALUES ({new_column_items});
END
"""
        pk_equal_items1 = self._get_pk_column_equal_string(
            left_table_alias="OLD",
            right_table_alias="NEW"
        )
        pk_equal_items2 = self._get_pk_column_equal_string(
            left_table_alias="`{database_name}`.`_{table_name}_new`".format(
                database_name=self.database_name,
                table_name=self.table_name
            ),
            right_table_alias="OLD"
        )
        return demo_sql.format(
            database_name=self.database_name,
            table_name=self.table_name,
            column_items=self._get_full_column_union_string(table_alias=""),
            new_column_items=self._get_full_column_union_string("NEW"),
            pk_equal_items1=pk_equal_items1,
            pk_equal_items2=pk_equal_items2
        )

    def _get_delete_trigger(self):
        demo_sql = """
CREATE TRIGGER `{database_name}`.`pt_osc_{database_name}_{table_name}_del` 
AFTER DELETE ON `{database_name}`.`{table_name}` 
FOR EACH ROW 
DELETE IGNORE FROM `{database_name}`.`_{table_name}_new` 
WHERE {pk_equal_items2}
"""
        pk_equal_items2 = self._get_pk_column_equal_string(
            left_table_alias="`{database_name}`.`_{table_name}_new`".format(
                database_name=self.database_name,
                table_name=self.table_name
            ),
            right_table_alias="OLD"
        )
        return demo_sql.format(
            database_name=self.database_name,
            table_name=self.table_name,
            pk_equal_items2=pk_equal_items2
        )

    def _check_pk_columns(self):
        if len(self.pk_columns) < 1:
            logger.error("未获取到目标表的主键,异常退出")
            exit(-1)
        pass

    def _check_exist_trigger(self):
        mysql_client = self._get_mysql_client()
        sql_script = """
        SELECT EVENT_MANIPULATION 
        FROM information_schema.TRIGGERS
        WHERE EVENT_OBJECT_SCHEMA = %s
        AND EVENT_OBJECT_TABLE = %s
        """
        sql_paras = [self.database_name, self.table_name]
        query_rows = mysql_client.mysql_query(sql_script=sql_script, sql_paras=sql_paras)
        if len(query_rows) > 0:
            logger.error("指定表上存在触发器,异常退出")
            exit(-1)
        pass

    def _check_exists_new_table(self):
        mysql_client = self._get_mysql_client()
        sql_script = """
        SELECT TABLE_SCHEMA,TABLE_NAME
        FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = %s
        AND TABLE_NAME = %s
        """
        new_table_name = "_{table_name}_new".format(table_name=self.table_name)
        sql_paras = [self.database_name, new_table_name]
        query_rows = mysql_client.mysql_query(sql_script=sql_script, sql_paras=sql_paras)
        if len(query_rows) > 0:
            logger.error("新表{}.{}已经存在,异常退出".format(self.database_name, new_table_name))
            exit(-1)
        pass

    def _check_reference_table(self):
        mysql_client = self._get_mysql_client()
        sql_script = """
        SELECT TABLE_SCHEMA,TABLE_NAME 
        FROM information_schema.KEY_COLUMN_USAGE
        WHERE REFERENCED_TABLE_SCHEMA = %s
        AND REFERENCED_TABLE_NAME = %s
        """
        sql_paras = [self.database_name, self.table_name]
        query_rows = mysql_client.mysql_query(sql_script=sql_script, sql_paras=sql_paras)
        if len(query_rows) > 0:
            logger.error("指定表上存在外键约束,异常退出")
            exit(-1)
        pass

    def _check_env(self):
        self._check_pk_columns()
        self._check_exist_trigger()
        self._check_exists_new_table()
        self._check_reference_table()

    @classmethod
    def _decorate_trigger(cls, trigger_sql):
        decorate_sql = """
################################################################
DELIMITER $$
{trigger_sql}
$$
DELIMITER ;
"""
        return decorate_sql.format(trigger_sql=trigger_sql)

    def _get_create_new_table(self):
        demo_sql = """
################################################################
CREATE TABLE `{database_name}`.`_{table_name}_new` LIKE `{database_name}`.`{table_name}`;
"""
        return demo_sql.format(database_name=self.database_name, table_name=self.table_name)

    def show_create_triggers(self):
        self._check_env()
        trigger_sqls = [
            self._get_insert_trigger(),
            self._get_delete_trigger(),
            self._get_update_trigger()
        ]
        sql_items = list()
        create_table_sql = self._get_create_new_table()
        sql_items.append(create_table_sql)
        for trigger_sql in trigger_sqls:
            trigger_sql = self._decorate_trigger(trigger_sql=trigger_sql)
            sql_items.append(trigger_sql)
        logger.info("创建新表和触发器的脚本如下:")
        logger.info("\r\n".join(sql_items))

    def show_drop_triggers(self):
        demo_sql = """
################################################################
DROP TRIGGER `{database_name}`.`pt_osc_{database_name}_{table_name}_ins`;
DROP TRIGGER `{database_name}`.`pt_osc_{database_name}_{table_name}_del`;
DROP TRIGGER `{database_name}`.`pt_osc_{database_name}_{table_name}_upd`;
DROP TABLE `{database_name}`.`_{table_name}_new`;
"""
        demo_sql = demo_sql.format(
            database_name=self.database_name,
            table_name=self.table_name
        )
        logger.info("删除新表和触发器的脚本如下:")
        logger.info(demo_sql)


def init_logger(with_debug=False):
    if with_debug:
        LoggerHelper.init_logger(logger_level=logging.DEBUG)
    else:
        LoggerHelper.init_logger(logger_level=logging.INFO)
    global logger
    logger = LoggerHelper.get_logger()


def main(database_name: str = "testdb", table_name: str = "tb1001",
         mysql_host: str = "127.0.0.1", mysql_port: int = 3306,
         mysql_user: str = "root", mysql_pass: str = "",
         with_debug: bool = False):
    """
    生产指定表的触发器脚本
    :param database_name: 数据库名称
    :param table_name: 数据表名称
    :param mysql_host: MySQL实例地址
    :param mysql_port: MySQL实例端口
    :param mysql_user: MySQL用户账号
    :param mysql_pass: MySQL用户密码
    :param with_debug: 是否开启DEBUG模式
    :return:
    """
    init_logger(with_debug=with_debug)
    mt = MySQLTrigger(
        database_name=database_name, table_name=table_name,
        mysql_host=mysql_host, mysql_port=mysql_port,
        mysql_user=mysql_user, mysql_pass=mysql_pass,
    )
    mt.show_drop_triggers()
    mt.show_create_triggers()


if __name__ == '__main__':
    fire.Fire(main)
